clear all
set more off, perm
local mydir "`1'"
global Tables 	`mydir'/TS/Tables
global Work   	`mydir'/TS/DataWork
global Raw    	`mydir'/TS/DataRaw
global WRDS  	`mydir'/WRDS
/* ********************************************************************* */
* Compute dividends based on CRSP data
/* ********************************************************************* */
import sas caldt totval spindx vwretd vwretx sprtrn using $WRDS/CRSP/msp500.sas7bdat, clear

gen datem=mofd(caldt)
format datem %tm

tsset datem

gen lnret=ln(1+sprtrn)
gen lnret1=0
forvalues x=1(1)12 {
	replace lnret1=lnret1+F`x'.lnret
}
gen yld=(1+vwretd)/(1+vwretx)-1
gen div=yld*spindx

tsset datem
gen sum=div
forvalues x=1(1)11 {
	quietly: replace sum=sum+L`x'.div if L`x'.div<.
}
gen g_d_crsp=ln(F12.sum)-ln(sum) 

order caldt sum g_d_crsp

gen lnr=ln(1+vwretd)
gen lnretsp=0
forvalues x=1(1)12 {
	quietly: replace lnretsp=lnretsp+F`x'.lnr if F`x'.lnr<.
}
* deal with holidays
keep caldt g_d_crsp sum lnretsp lnret* spindx
tsset caldt
tsfill
foreach var of varlist lnret lnret1 g_d_crsp sum spindx  {
	replace `var'=L.`var' if `var'==.
}
rename g_d_crsp gdiv_crsp
rename sum 	div_crsp
rename caldt	date

save $Work/divs, replace
/* ********************************************************************* */
/* Get Shiller's data on earnings and dividends				 */
/* ********************************************************************* */
* Get Shiller's data on CAPE (http://www.econ.yale.edu/~shiller/data.htm)
import excel $Raw/shiller_updated.xls, sheet("formatted") firstrow clear
keep if Date<.
drop Fraction

foreach var of varlist RealDiv* RealEar* CAPE CAPD D E {
	destring `var', replace
}
drop RC* CPI Rate* Real*

gen yr=int(Date)
gen mo=100*(Date-yr)

generate date=mdy(mo+1,1,yr  )-1 	if mo<=11
replace  date=mdy(1   ,1,yr+1)-1	if mo==12
format date %td

drop yr mo Date
order date

gen datem=mofd(date)
format datem %tm

tsset datem
tsfill
ipolate E datem, gen(LTME)
ipolate D datem, gen(LTMD)
keep date datem LTM*
/* ********************************************************************* */
merge 1:1 date using $Work/divs
drop if _merge==2
drop _merge 
d lnret*
save $Work/shiller, replace
/* ********************************************************************* */
* MERGE WITH IBES EPS DATA
merge 1:1 datem using $Work/cs01_tot
keep if _merge==3
drop _merge 
/* ********************************************************************* */
* [4] Get Compustat data on actuals
* put e(t+1) at time=t
merge 1:1 datem using $Work/cs02_tot
keep if _merge==3
drop _merge date
/* ********************************************************************* */
save $Work/cs03, replace
* *******************************